package sueldos;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.eclipse.swt.SWT;
import org.eclipse.swt.widgets.Table;
import org.eclipse.swt.widgets.TableItem;

import com.swtdesigner.SWTResourceManager;

public class Uatre {

	/**
	 * @param args
	 */
	
	public ResultSet datosUatre(String periodo){
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conexion = DriverManager.getConnection("jdbc:mysql://localhost:3306/huaicos", "", ""); 
			String sql = "SELECT empleado.CUIL, " +
     		"SUM(liquidac.REM_APORTE) AS importe FROM " +
     		"huaicos.empleado INNER JOIN huaicos.liquidac  " +
     		"ON (empleado.LEGAJO = liquidac.LEGAJO) " +
     		"WHERE (liquidac.PERIODO = ? and empleado.cuil <> '20-16370604-1') " +
     		"GROUP BY empleado.LEGAJO, liquidac.PERIODO";
			
	        PreparedStatement ps = conexion.prepareStatement(sql);
	        ps.setString(1, periodo);
	        ps.execute();
	        
	        return ps.getResultSet();
			
			
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;

	}
	
	
	
	public void empleadosActivos(String base, Table tabla){
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conexion = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+base, "", ""); 
			String sql = "SELECT DISTINCT cuil, CONCAT(apellido,', ',nombres) " +	"AS apenom, " +
					"fechaegr FROM empleado WHERE (FECHAEGR IS NULL AND legajo > 0)  " +
					"GROUP BY legajo ORDER BY CUIL ASC";
			
	        PreparedStatement rs = conexion.prepareStatement(sql);
	        
	        rs.execute();
	       
			 while (rs.getResultSet().next()){
				 if (rs.getResultSet().getBoolean(3)!=true){
					 TableItem item = new TableItem(tabla, SWT.NONE);
					 item.setText(0, rs.getResultSet().getString(1));
					 item.setText(1, rs.getResultSet().getString(2));
				 }else{
					 TableItem item = new TableItem(tabla, SWT.NONE);
					 item.setForeground(SWTResourceManager.getColor(SWT.COLOR_RED));
					 item.setText(0, rs.getResultSet().getString(1));
					 item.setText(1, rs.getResultSet().getString(2));
				 }
	         }
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public ResultSet datosUatre(String periodo, String base){
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conexion = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+base, "", ""); 
			String sql = "SELECT empleado.CUIL," +
					"SUM(DISTINCT liquidac.REM_APORTE) AS importe, " +
					"CASE WHEN (empleado.obrasocial) = 3 THEN 'si' ELSE 'no' END AS sindicato " +
					"FROM "+base+".empleado INNER JOIN "+base+".liquidac  " +
					"ON (empleado.LEGAJO = liquidac.LEGAJO) WHERE (liquidac.PERIODO = ?) " +
					"GROUP BY empleado.LEGAJO, liquidac.PERIODO";
			/*String sql = "SELECT empleado.CUIL, " +
     		"SUM(DISTINCT liquidac.REM_APORTE) AS importe FROM " +
     		base+".empleado INNER JOIN "+base+".liquidac  " +
     		"ON (empleado.LEGAJO = liquidac.LEGAJO) " +
     		"WHERE (liquidac.PERIODO = ?) " +
     		"GROUP BY empleado.LEGAJO, liquidac.PERIODO";*/
			
	        PreparedStatement ps = conexion.prepareStatement(sql);
	        ps.setString(1, periodo);
	        ps.execute();
	       
	        return ps.getResultSet();
			
			
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;

	}
	

	public void empleadosPorEmpresa(String cuit, Table table){
		try {
			 String userName = "";
	         String password = "24909558leandro25086";
	         String url = "jdbc:odbc:baseUatre";
	         Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
	         Connection conn = DriverManager.getConnection (url, userName, password);
	         String sql = "select CUIL, ApellidoNombres, Baja, retencion1 from view_trabajador where CUIT = ?";
			 
			 PreparedStatement rs = conn.prepareStatement(sql);
			 rs.setString(1, cuit);
			 rs.execute();
			 
			 while (rs.getResultSet().next()){
				 if (rs.getResultSet().getBoolean(3)!=true){
					 TableItem item = new TableItem(table, SWT.NONE);
					 item.setText(0, rs.getResultSet().getString(1));
					 item.setText(1, rs.getResultSet().getString(2));
					 
					 if (rs.getResultSet().getBoolean(4) !=true){
						 item.setText(2, "NO");
					 }else {
						 item.setText(2, "SI");
					 }
					 
				 }else{
					 TableItem item = new TableItem(table, SWT.NONE);
					 item.setForeground(SWTResourceManager.getColor(SWT.COLOR_RED));

					 item.setText(0, rs.getResultSet().getString(1));
					 item.setText(1, rs.getResultSet().getString(2));
					 
					 if (rs.getResultSet().getBoolean(4) !=true){
						 item.setText(2, "NO");
					 }else {
						 item.setText(2, "SI");
					 }
				 }
	         }
			rs.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public void borrarEmpleado(boolean estado, String cuil){
		try {
			String userName = "";
			String password = "24909558leandro25086";
			String url = "jdbc:odbc:baseUatre";
			Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
			Connection conn = DriverManager.getConnection (url, userName, password);
			String sql = "update view_trabajador set Baja = ?  where cuil = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setBoolean(1, estado);
			ps.setString(2, cuil);
			ps.execute();
			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	public void cuotaSindical(boolean estado, String cuil){
		try {
			String userName = "";
			String password = "24909558leandro25086";
			String url = "jdbc:odbc:baseUatre";
			Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
			Connection conn = DriverManager.getConnection (url, userName, password);
			String sql = "update view_trabajador set retencion1 = ?  where cuil = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setBoolean(1, estado);
			ps.setString(2, cuil);
			ps.execute();
			ps.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	
	
	
	public void actualizaUatre(ResultSet rs){
		try {
			 String userName = "";
	         String password = "24909558leandro25086";
	         String url = "jdbc:odbc:baseUatre";
	         Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
	         Connection conn = DriverManager.getConnection (url, userName, password);
	         String sql = "update view_trabajador set remuneracion = ?  where cuil = ?";
			 

			 PreparedStatement ps2 = conn.prepareStatement(sql);
	        
			 rs.beforeFirst();			 
			 while (rs.next()){
		         ps2.setBigDecimal(1, rs.getBigDecimal(2));
				 ps2.setString(2, rs.getString(1));
				 ps2.executeUpdate();
	         }
			rs.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public String obtieneBase(String cuil) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conexion = DriverManager.getConnection("jdbc:mysql://localhost:3306/empresa", "", ""); 
			String sql = "SELECT base FROM registroempresa WHERE cuit = ?";
	        PreparedStatement ps = conexion.prepareStatement(sql);
	        ps.setString(1, cuil);
	        ps.execute();
	        ps.getResultSet().next();
	        return ps.getResultSet().getString(1);
			
			
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;


	}
	


	
	

	
}
